package love.yuanmengrobot.als.utilPublic;
/*
 *   @Studio:CHJY · 源梦科技
 *   @Author:CHJY创幻
 *   @Ide:IntelliJ IDEA
 *   @Creation time:2021/5/24 22:37
 */

import love.yuanmengrobot.als.business.javaBean.ScoreView;

public class SqlString {

    /*
     * 已知账号,密码
     * @return 查询sql
     * @a 账号 b MD5密码*/
    public static String selectUsetSql(String a, String b) {
        return "select USERID,USERPHONE,USEREMAIL from alsuser where username='" + a + "' and userpassword='" + b + "'";
    }

    /*
     * 查询所有公告
     * @return 查询sql
     * @*/
    public static String selectAnnouncementSql() {
        return "SELECT NOTICE_ID,PUBLISHER,TITLE,NOTICE_CONTENT,NOTICE_DATE FROM alsannouncement ORDER BY NOTICE_DATE DESC";
    }

    /*
     * 插入新公告
     * @return 查询sql
     * @*/
    public static String insertAnnouncementSql(String PUBLISHER, String TITLE, String CONTENT) {
        return "insert into alsannouncement values(null,'" + PUBLISHER + "','" + TITLE + "','" + CONTENT + "',sysdate)";
    }

    /*
     * 删除公告
     * @return 查询sql
     * @*/
    public static String deleteAnnouncementSql(String NOTICE_ID) {
        return "DELETE FROM alsannouncement WHERE NOTICE_ID=" + NOTICE_ID + "";
    }

    /*
     * 修改公告
     * @return 查询sql
     * @*/
    public static String updateAnnouncementSql(String NOTICE_ID, String TITLE, String CONTENT) {
        return " UPDATE alsannouncement SET TITLE='" + TITLE + "',NOTICE_CONTENT='" + CONTENT + "',NOTICE_DATE=sysdate WHERE NOTICE_ID=" + NOTICE_ID + "";
    }


    //
    /*
     * 已知学生学号,查询学生已修的课程名称和成绩
     * @return 查询sql
     * @stuNo 学生学号*/
    public static String selectCourseNameAndScoreSql(String stuNo) {
        return "select course_name,score from score where student_id = " +
                "(select student_id from student where stu_no = " +
                stuNo + ")";
    }

    /*
     * 已知学生学号,查询学生需要选修的课程名称
     * @return 查询sql
     * @stuNo 学生学号*/
    public static String selectCourseName(String stuNo) {
        return "select course_name from alstrainplancourse where trainplan_id = " +
                "(select trainplan_id from alstrainplan where (major_id , grade) in " +
                "(select major_id,grade from student where stu_no = " +
                stuNo + "))";
    }


    /*
     * 已知学生学号,查询学生每年所修的学分
     * @return 查询sql
     * @stuNo 学生学号*/
    public static String selectCourseTermScore(String stuNo) {
        return "SELECT COURSE_TERM,sum(CREDIT) CREDIT FROM ALSSCORE,ALSSTUDENT" +
                " WHERE STU_NO=" + stuNo +
                " AND ALSSCORE.STUDENT_ID=ALSSTUDENT.STUDENT_ID" +
                " and COURSE_TYPE in('学科基础','通识必修','实践环节','专业任选','专业限选','专业基础必修')" +
                " and (CASE 	" +
                "WHEN BhToNumber(SCORE)=-1 THEN	" +
                "(CASE	WHEN SCORE in ('及格','优','良','中') THEN		60	ELSE		59 END )" +
                "	ELSE BhToNumber(SCORE) END )>=60 GROUP BY COURSE_TERM ORDER BY COURSE_TERM";
    }

    /**
     * 插入数据到major专业表
     *
     * @param majorName 专业名称
     * @return 插入数据SQL
     */
    public static String insertDataIntoMajor(String majorName) {
//        return "insert into major values(null,'" + majorName + "')";
        return "insert into majortest values(majortest_id_seq.nextval,'" + majorName + "')";
    }

    /**
     * 插入数据到student学生表
     *
     * @param scoreView 成绩中的学生
     * @return SQL
     */
    public static String insertDataIntoStudent(ScoreView scoreView) {
        return "insert into student values (null,'"
                + scoreView.getStu_no() + "' , '"
                + scoreView.getStu_name() + "' , ( select major_id from major where major_name = '"
                + scoreView.getMajor_name() + "' ) , '"
                + scoreView.getGrade() + "' , '"
                + scoreView.getClazz() + "' ) ";
    }

    /**
     * 加入数据到score成绩表
     *
     * @param scoreView  学生成绩
     * @return SQL
     */
    public static String insertDataIntoScore(ScoreView scoreView) {
        return "insert into score values ( null , ( select student_id from student where stu_no = '"
                + scoreView.getStu_no() + "' ) , '"
                + scoreView.getCourse_id() + "' , '"
                + scoreView.getScore() + "' , '"
                + scoreView.getScore_type() + "' , '"
                + scoreView.getCourse_code() + "' , '"
                + scoreView.getCourse_term() + "' , '"
                + scoreView.getCourse_name() + "' , '"
                + scoreView.getTest_type() + "' , '"
                + scoreView.getCredit() + "' , '"
                + scoreView.getCourse_type() + "' ) ";
    }

    /**
     * 通过专业名称查询专业信息
     *
     * @param majorName 专业名称
     * @return SQL
     */
    public static String selectMajorByMajorName(String majorName) {
        return "select major_id from major where major_name = '" + majorName + "'";
    }

    /*
     *
     * @return 插入数据sql*/

    /**
     * 通过学生学号查询学生信息
     *
     * @param stuNo 学号
     * @return SQL
     */
    public static String selectStudentByStuNo(String stuNo) {
        return "select student_id from student where stu_no = '" + stuNo + "'";
    }

    /*
     * 通过学生学号查询学生信息
     * @return 插入数据sql*/
    public static String selectStudentNameById(String stuNo) {
        return "select student_id,STU_NO,MAJOR_ID,STU_NAME,GRADE,CLAZZ from student where stu_no = '" + stuNo + "'";
    }

    /**
     * 通过学生学号和课号查询学生成绩信息
     *
     * @param stuNo    学号
     * @param courseId 课号
     * @return SQL
     */
    public static String selectScoreByStuNo(String stuNo, String courseId) {
//        return "select score_id from score where student_id = "
//                + "( select student_id from student where stu_no = '"
//                + stuNo +"' ) and course_id = '" + courseId +"'";
        return "select score_id from scoretest where student_id = "
                + "( select student_id from student where stu_no = '"
                + stuNo + "' ) and course_id = '" + courseId + "'";
    }

    /*
     * 通过学号查询某个学生的已修课程的对应成绩、对应学分和课程类型
     * @return 查询数据sql
     */
    public static String selectCredit(String stuNo) {
        return "select score,credit,course_type from score where student_id=(select student_id from student where stu_no='" + stuNo + "')";
    }

    /**
     * 通过专业id查找培养方案信息
     *
     * @param majorId 专业id
     * @return 返回查询sql
     */
    public static String selectTrainPlan(String majorId) {
        return "select * from alstrainplan where major_id=" + majorId;
    }

    /**
     * 通过学生学号查询学生选课和培养方案全连接查询
     *
     * @param stuNo
     * @return 返回查询sql
     */
    public static String selectPlanFullJoinScore(String stuNo) {
        return "SELECT e.COURSE_NAME PlanCourseName ,g.COURSE_NAME CourseName,SCORE,NVL(e.COURSE_TYPE, g.COURSE_TYPE) COURSE_TYPE,e.OPTIONAL_CREDIT,e.LIMITE_CREDIT,g.COURSE_TERM,NVL(e.CREDIT, g.CREDIT) CREDIT ,(PROFESSIONAL_REQUIRED+GENERAL_ELECTIVE+GENERAL_REQUIRED+DISCIPLINE_BASIS+PRACTICE_LINK) required "
                + "FROM ("
                + "(select * "
                + "FROM  ALSSTUDENT b INNER JOIN ALSTRAINPLAN c on b.MAJOR_ID=c.MAJOR_ID and b.GRADE=c.GRADE INNER JOIN ALSTRAINPLANCOURSE d on d.TRAINPLAN_ID=c.TRAINPLAN_ID "
                + "WHERE b.STUDENT_ID=21) e "
                + "FULL JOIN "
                + "(SELECT * FROM ALSSCORE f INNER JOIN ALSSTUDENT h ON f.STUDENT_ID=h.STUDENT_ID WHERE h.STU_NO='" + stuNo + "') g on "
                + "e.COURSE_NAME= g.COURSE_NAME)";
    }

    public static String selectAverageEnglish(String stuNo) {
        return "SELECT AVG(SCORE) "
                + "FROM ALSSTUDENT,ALSSCORE "
                + "WHERE ALSSTUDENT.STUDENT_ID=ALSSCORE.STUDENT_ID and ALSSTUDENT.STU_NO='1900710140' and (CASE "
                + "WHEN instr(COURSE_NAME,'大学英语')>0 THEN 1\tELSE\t0 END=1)";
    }


    public static String updatePassword(String userId, String p) {
        return "UPDATE alsuser SET USERPASSWORD ='" + p + "' WHERE userid='" + userId + "'";
    }

    public static String getAlsusers() {
        return "SELECT * FROM alsuser";
    }

    public static String addAlsuser(String phone, String name, String password, String email) {
        return "INSERT INTO alsuser VALUES(''," + phone + ",'" + name + "','" + password + "','" + email + "')";
    }

    public static String deleteAlsuser(String id) {
        return "DELETE FROM alsuser WHERE userid=" + id;
    }

    /**
     * 删除SCORE表中指定ID的行
     *
     * @param id 数据ID
     * @return SQL
     */
    public static String deleteScoreById(String id) {
        return "delete from score where score_id=" + id;
    }

    /**
     * 获取类似excel数据源的分页数据的总页数
     *
     * @param id   学号
     * @param name 姓名
     * @return sql
     */
    public static String selectScorePage(String id, String name) {
        String sql = "select count(score_id) from score,student where student.student_id=score.student_id";
        if (!("".equals(id) || id == null)) {
            sql = sql + " and stu_no=" + id;
        }
        if (!("".equals(name) || name == null)) {
            sql = sql + " and stu_name='" + name + "'";
        }
        return sql;
    }

    /**
     * 查询类似excel数据源的分页数据
     *
     * @param id   学号
     * @param name 姓名
     * @return sql
     */
    public static String selectScoreByStuNoOrStuName(String id, String name, int page, int perPage) {
        String sql = "select score_id,course_term,stu_no,stu_name,major_name,grade,clazz,course_code,course_id," +
                "course_name,score,credit,test_type,score_type,course_type from student,major,score " +
                "where student.student_id=score.student_id and student.major_id=major.major_id";
        if (!("".equals(id) || id == null)) {
            sql = sql + " and stu_no=" + id;
        }
        if (!("".equals(name) || name == null)) {
            sql = sql + " and stu_name= '" + name + "'";
        }
        sql = sql + " order by stu_no,course_term";
        /*分页*/
        sql = "select * from (select e.*,rownum rn from ( " + sql + ") e where rownum<=" + perPage * page + ") " +
                "where rn >= " + ((page - 1) * perPage + 1);
        return sql;
    }

    /**
     * 修改成绩
     * @param id 成绩ID
     * @param score 成绩
     * @return SQL
     */
    public static String updateScore(String id, String score) {
        return "update score set score=" + score + " where score_id=" + id;
    }
}

